NorthwindCE Sample

The NorthwindCE sample application illustrates how to develop a mobile application capable of offline storage using Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE) and the .NET Compact Framework. The application highlights the design of forms and controls, data access against a local SQL Server CE database, and synchronization to a SQL Server database through replication. NorthwindCE is made up of four controls embedded into a single form, FormNorthwindCE.cs. Each NorthwindCE control contains complete and thoroughly documented source code that can be reused as templates when designing new applications.

The four controls in the application are: ControlCustomer, ControlEmployees, ControlOrders, and ControlReplication, which are referred to, respectively, as the Customer control, Employees control, Orders control, and Replication control. The Customer control, Employees control, and Orders control read data from and write data to the Customers, Employees, and Orders tables in a copy of the Northwind database on the device.Customer and order data can be accessed and employee information updated using these controls. The Replication control accepts connection settings and uses this information to connect to a remote SQL Server database. The local database is then synchronized to the server database through replication.

NorthwindCE is designed for Pocket PC devices and the Pocket PC emulator. Modify the user interface of the sample application for use with other devices.

Requirements

To run NorthwindCE, you must already have the following software installed on your computer:

·         SQL Server 2000 Service Pack 1 or higher

·         Internet Information Services (IIS)

For more information, see "Installing Internet Information Services and Internet Information Server" in Visual Studio .NET Software Requirements!ALink("vxtbsVisualStudioNETSoftwareRequirements").

·         SQL Server CE (included with Visual Studio .NET)

·         SQL Server CE 2.0 Server Tools

The SQL Server CE 2.0 Server Tools installers, sqlce20sql2ksp1.exe for SQL Server Service Pack 1 and sqlce20sql2ksp2.exe for SQL Server Service Pack 2, come with Visual Studio .NET. These files are located by default in C:\Program Files\Microsoft Visual Studio .NET\CompactFrameworkSDK\v1.0.5000\Windows CE\. The SQL Server CE Connectivity Management utility and the SQL Server CE Virtual Directory Creation Wizard are automatically installed by running one of the SQL Server CE Server Tools installers on the computer running IIS.

 

At the end of the setup, you can optionally launch the SQL Server CE Virtual Directory Creation Wizard to configure the computer running IIS. Alternatively, you can go to the Start menu, point to Programs, Microsoft SQL Server CE 2.0, and then click Configure Connectivity Support in IIS. Launch the wizard by double-clicking Create a Virtual Directory in the right pane of the SQL Server CE Management utility.  To configure the computer running IIS, follow the instructions provided by the wizard.

 

Configuring SQL Server

After Microsoft® Internet Information Services (IIS) is properly configured, you can enable Microsoft SQL Server 2000 for replication. The replication sample application uses a duplicated Northwind database called Nwind_SQLCE. Running the setup script SetupRepl.bat on the computer running SQL Server 2000 creates the Nwind_SQLCE database, enables SQL Server for replication, and creates a publication named SQLCEReplDemoNet.

To configure the computer running SQL Server 2000, perform the following steps. If the computer running SQL Server is already configured as a Publisher/Distributor for replication and has a snapshot folder defined with the appropriate permissions, skip Step 1 and Step 2.

1.      Create a shared directory on the computer running SQL Server 2000 that will become the Publisher/Distributor. Replication snapshot files are created in this directory by the replication process and are read by the SQL Server CE Subscriber.

2.      Ensure that the IIS user specified during synchronization has read access to the shared directory. By default, the virtual directory is configured with anonymous access; therefore, grant computername\IUSR_computername read access to the shared directory created in Step 1.

3.      Run SetupRepl.bat, located with the NorthwindCE sample:

Usage: SetupRepl {required parms} {optional parms}

Required Arguments:

{snapshot share} - a share available for the storage of snapshot files. All Subscribers (IIS user) must be able to read from this share.

{publisher login} - Publisher user ID.

{publisher password} - Publisher password.

Optional Arguments:

{publisher} - Publisher name. Defaults to local computer.

{snapshot server name} - Defaults to Publisher name (Must be set to support named instances).

Examples:

SetupRepl ReplSnapshot sa password

SetupRepl ReplSnapshot sa password Publisher

SetupRepl ReplSnapshot sa password Publisher\instance ServerName

 

This step installs a Publisher with a local Distributor, creates a database named Nwind_SQLCE, and populates it with initial data that matches data in the Northwind database that ships with SQL Server. It also creates a merge publication named SQLCEReplDemoNet from the Nwind_SQLCE database.

The Distributor is installed using the shared directory specified when the Setuprepl.bat file is run. This shared directory is used as the replication snapshot folder. For example, if you call Setuprepl.bat and specify ReplSnapshot as the snapshot shared directory, the directory for all snapshot files is \\computername\ReplSnapshot.

4.      Grant the IIS user (NT_Authentication) or the SQL Server user (DB_Authentication) access to the publication access list (PAL).

 

Building and Launching the Sample

This sample application is available in Visual Basic and Visual C#. After completing the following procedures you can run the application.

To build and launch the sample

1.      Copy the NorthwindCE sample files into a solution directory on your development computer.

The Visual Basic files are located in Program Files\Microsoft Visual Studio .NET\CompactFrameworkSDK\v1.0.5000\Samples\PocketPC\VB\NorthwindCE.

The Visual C# files are located in Program Files\Microsoft Visual Studio .NET\CompactFrameworkSDK\v1.0.5000\Samples\PocketPC\VC#\NorthwindCE.

2.      On the File menu, click Open Solution, and load NorthwindCE.sln.

3.      In the Deployment Device box on the Device toolbar, select the device you want to target.

4.      On the Debug menu, click Start Without Debugging.

The application is compiled, deployed to the target device (along with any additional libraries required for run time), and launched on the device. On the device, the executable is located at \Windows\Start Menu\NorthwindDemo.exe.

Using the Application

There is a standard menu at the bottom of the screen for all four controls (Replication, Customers, Employees, and Orders). The menu has the following options:

 

Menu

Actions

File

Exit : Closes the NorthwindCE application.

Northwind

Employees : Loads the Employees control.

Customers : Loads the Customers control.

Sync

Replication : Loads the Replication control.

 

Replication Control

The Replication control allows you to synchronize to a SQL Server database and replicate information to a local database. Navigate to this control using the Replication command on the Sync menu. The Replication control is always displayed first;  it checks whether there is a local database containing the necessary data for the other controls.  Because the Customers, Employees, and Orders controls access data from the local database, the local database must be present for those controls to function. If a local database does not exist, the Northwind menu option is disabled to prevent access to other controls. If a local database exists, the fields in this control are populated with default values from the database.

The following properties are required for replication with a SQL Server database.

Property

Value

Comment

Authentication

<SQL> or <Windows>

SQL authentication requires a user ID and password to be specified. Windows Authentication does not require a user ID and password. .

Publisher

<Database Server Name>

The name of the computer running SQL Server.

User ID

<default sa>

The user ID is required only if SQL authentication is used.

Password

<default blank>

A password is required only if SQL Server authentication is used.

Internet URL

http://<IIS Server Name>/ <virtual directory>/sscesa20.dll

This URL points to the location of the SQL Server CE 2.0 Server Agent file (sscesa20.dll). The IIS server name is the name of the computer running IIS. The virtual directory is the same directory name used when configuring IIS through the SQL Server CE Virtual Directory Creation Wizard.

Internet Login

<default blank>

The Internet login is required only if security is set on IIS to require a login and password.

Internet Password

<default blank>

The Internet password is required only if security is set on IIS to require a login and password.

Subscriber

<SQLCESub#1>

The Subscriber field is used to identify the Subscriber.

 

There are two buttons on this control: Initialize and Reset. Initialize begins a replication session to the specified Publisher using the specified connection settings. A local database, NorthwindDemo.sdf is created. The connection fields are then disabled after replication has finished. Reset deletes the local NorthwindDemo database and enables the connection setting fields so they can be changed.

A sample copy of the NorthwindDemo.sdf SQL Server CE database is included with the NorthwindCE Visual Studio.NET project. When NorthwindDemo.sdf is copied onto the device, you do not have to complete the steps above to configure replication before you can begin using the NorthwindCE application. All features of the NorthwindCE application will be available except for synchronization using replication. To use replication, you must click Reset and then complete all configuration steps listed above.

To copy NorthwindDemo.sdf onto the device, open the NorthwindCE.sln Visual Studio.NET solution. Right click on the NorthwindDemo.sdf file in the Solutions Explorer and select Properties. In the Properties window, click on Build Action and select Content from the drop down box. Open the file FormNorthwindCE.vb or FormNorthwindCE.cs depending on which version you have and follow the instructions in comments within the constructor of the class before building the application.

Employees Control

The Employees control allows you to select employee data from the Employees table in the local NorthwindDemo database (NorthwindDemo.sdf). Selecting an employee name from the combo box retrieves the corresponding Title, Address, City, Region, PostalCode, HomePhone, HireDate, and EmployeeID column values from the Employees table. All columns are editable except HireDate and EmployeeID. Clicking the Save button persists the changes to the local SQL Server CE database.

Customer Control

The Customer control allows you to select customer data from the Customers table in the local NorthwindDemo database. Selecting a customer name from the combo box retrieves the corresponding Address, City, PostalCode, Country, Phone, and ContactTitle column values from the Customers table. None of the fields on the Customer control are editable; the Customers table is read-only.

Orders Control

The Orders control allows you to select orders and order details from the Orders and OrderDetails tables in the local NorthwindDemo database. Selecting a customer name from the combo box retrieves the order records for that customer; the OrderID, OrderDate, RequiredDate, ShippedDate, and ShipVia column values are displayed. Selecting a specific order brings up the corresponding records for that order from the OrderDetails table; the ProductID, UnitPrice, Quantity, and Discount column values are displayed. None of fields on the Orders control are editable; both the Orders and OrderDetails tables are read-only.